package xlcao.sohutv.db;

import java.util.ArrayList;

import xlcao.sohutv.bean.Const;
import xlcao.sohutv.bean.ProgramData;
import xlcao.sohutv.bean.TVInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.BitmapFactory;
import android.util.Log;

public final class DBOperator extends SQLiteOpenHelper {

	private static final String CREATE_TABLE1 = "CREATE TABLE tvlist ( "
			+ "id INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,"
			+ "title TEXT  NOT NULL," + "url TEXT  NOT NULL,"
			+ "mode INTEGER DEFAULT '0' NOT NULL, "
			+ "isCollected INTEGER DEFAULT '0' NOT NULL); ";

	private static final String TAG = "TV_DB";
	private static SQLiteDatabase mDB = null;

	public DBOperator(Context context) {
		super(context, "TVInfo.db", null, 1);
		if (mDB == null) {
			mDB = this.getReadableDatabase();
        }
        
	}

	public boolean insertRow(final TVInfo item) {
		mDB = getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("title", item.getTitle());
		values.put("url", item.getUrl());
		values.put("mode", item.getMode());
		values.put("isCollected", item.getIsCollected());
		values.put("type", item.getType());
		values.put("channelID", item.getChannelID());
		return (mDB.insert(Const.TVLIST_TABLE_NAME, null, values) > 0);
	}

	public boolean insertChannelRow(String channelID, ProgramData item) {
		mDB = getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("channelID", channelID);
		values.put("date", item.getDate());
		values.put("time", item.getTime());
		values.put("detail", item.getDetails());
		return (mDB.insert(Const.CHANNELINFO_TABLE_NAME, null, values) > 0);
	}

	public boolean updateRow(TVInfo item) {

		String update = "update "+ Const.TVLIST_TABLE_NAME
				+ " SET isCollected = " + item.getIsCollected()
				+ " WHERE  id = " + item.getId()
				+ " AND type = " + item.getType();
	
		
		mDB = getWritableDatabase();
		mDB.execSQL(update);
		return false;
	}

	private boolean deleteAllRows() {

		boolean SUCCESS = mDB.delete(Const.TVLIST_TABLE_NAME, "id>=0", null) > 0;
		return SUCCESS;
	}

	public boolean deleteAllChannelRows() {

		boolean SUCCESS = mDB.delete(Const.CHANNELINFO_TABLE_NAME, null, null) > 0;
		mDB.close();
		return SUCCESS;
	}

	public boolean deleteFavRows() {
		mDB = getReadableDatabase();
		final ContentValues args = new ContentValues();
		args.put("isCollected", 0);

		return mDB
				.update(Const.TVLIST_TABLE_NAME, args, "isCollected= 1", null) > 0;
	}

	public String getVersion() {
		try {
			mDB = getReadableDatabase();
			Cursor c = mDB.query(Const.DBVERSION_TABLE_NAME,
					new String[] { "version" }, null, null, null, null, null);
			int numRows = c.getCount();
			if (numRows == 0) {
				Log.e(TAG, "no data in db");
				return "";
			}
			c.moveToFirst();
			String ver = c.getString(0);
			c.close();
			mDB.close();
			return ver;
		} catch (Exception e) {
			Log.e(TAG, "Error in getting items from db: " + e);
			return "";
		}
	}

	public ArrayList<TVInfo> getItems(int collecttype) {
		mDB = this.getReadableDatabase();
		// type 0 all ,type 1 fav
		ArrayList<TVInfo> dbRows = new ArrayList<TVInfo>();
		try {

			
			Cursor c = mDB.query(Const.TVLIST_TABLE_NAME, new String[] { "id",
					"title", "url", "mode", "isCollected", "icon", "type",
					"channelID" }, "isCollected =" + collecttype,
					null, null, null, "type,id");

			int numRows = c.getCount();
			if (numRows == 0) {
				Log.e(TAG, "no data in db");
				return null;
			}
			c.moveToFirst();

			for (int i = 0; i < numRows; ++i) {
				TVInfo item = new TVInfo();
				item.setId(c.getInt(0));
				item.setTitle(c.getString(1));
				item.setUrl(c.getString(2));
				item.setMode(c.getInt(3));
				item.setIsCollected(c.getInt(4));
				byte[] icon = c.getBlob(5);
				if (icon != null) {
					item.setIcon(BitmapFactory.decodeByteArray(icon, 0,
							icon.length));
                }
                
				else {
					item.setIcon(null);
                }
                
				item.setType(c.getInt(6));
				item.setChannelID(c.getString(7));
				dbRows.add(item);
				c.moveToNext();
			}
			c.close();
			mDB.close();
		} catch (Exception e) {
			Log.e(TAG, "Error in getting items from db: " + e);
		}
		return dbRows;
	}

	public ArrayList<ProgramData> getChannelInfoByID(String channedID,
			String date) {

		ArrayList<ProgramData> dbRows = new ArrayList<ProgramData>();
		try {

			String[] args = { channedID, date };
			String query = "select channelID,time,detail from "
					+ Const.CHANNELINFO_TABLE_NAME
					+ " where channelID = ? and date = ? " + "order by time";

			Cursor c = mDB.rawQuery(query, args);

			int numRows = c.getCount();
			if (numRows == 0) {
				Log.e(TAG, "no channel data  in db");
				return null;
			}
			c.moveToFirst();

			for (int i = 0; i < numRows; ++i) {
				ProgramData item = new ProgramData();
				item.setChannelID(c.getString(0));
				item.setDate(c.getString(1));
				item.setTime(c.getString(2));
				item.setDetails(c.getString(3));
				dbRows.add(item);
				c.moveToNext();
			}
			c.close();
			mDB.close();
		} catch (Exception e) {
			Log.e(TAG, "Error in getting getString items from db: " + e);
		}
		return dbRows;
	}

	public ArrayList<String> getAllChannels() {
		ArrayList<String> dbRows = new ArrayList<String>();
		try {

			Cursor c = mDB.query(Const.TVLIST_TABLE_NAME,
					new String[] { "channelID" }, null, null, null, null, null);

			int numRows = c.getCount();
			if (numRows == 0) {
				Log.e(TAG, "no channel id in db");
				return null;
			}
			c.moveToFirst();

			for (int i = 0; i < numRows; ++i) {

				dbRows.add(c.getString(0));
				c.moveToNext();
			}
			c.close();
			mDB.close();
		} catch (Exception e) {
			Log.e(TAG, "Error in getting channelid from db: " + e);
		}
		return dbRows;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.e(TAG, "SQLitehelper onCreate!");
		try {

			// mDB.execSQL(CREATE_TABLE1);
			// Log.e(TAG, "createDataTable OK!");

		} catch (SQLException se) {
			se.printStackTrace();
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		String sql = "drop table if exists " + Const.TVLIST_TABLE_NAME + "";
		mDB.execSQL(sql);
		onCreate(db);

	}

}
